/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package scheduler;

/**
 *
 * @author Admin
 *//*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */


import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Vector;
/**
 *
 * @author Musikerostudent.accdb
 */
public class DbEngine {
    static String conStr = "jdbc:odbc:DRIVER={Microsoft Access Driver (*.mdb, *.accdb)};DBQ=D:\\DatabaseUpdate.accdb";
    
    
    public Vector getStudent() throws Exception{
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

        Vector<Vector<String>> studentVector = new Vector<Vector<String>>();
        
        Connection connection = DriverManager.getConnection(conStr);
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM Student WHERE CLASSCODE= '9572' ORDER BY 2");
        PreparedStatement preparedStatement2 = connection.prepareStatement("UPDATE Student SET Student.[Dropped] = 'Y' "
                + "WHERE (([studentID] = ?))");
        ResultSet result = preparedStatement.executeQuery();
        
        String id;
        String firstName;
        String lastName;
        String middleName;
        String tardies;
        String absences;
        while(result.next()){
            id = result.getString(1);
            lastName = result.getString(2);
            firstName = result.getString(3);
            middleName = result.getString(4);
            absences = result.getString(8);
            tardies = result.getString(9);
            Vector<String> student = new Vector<String>();
            student.add(id);//student id
            student.add(lastName);//student lastName
            student.add(firstName);//student firstName
            student.add(middleName);//student middleName
            student.add(absences);//student absences
            student.add(tardies);//student tardies
            

            if(absences.equalsIgnoreCase("5") || absences.equalsIgnoreCase("7") || absences.equalsIgnoreCase("10")
                  || absences.equalsIgnoreCase("12")){
                preparedStatement2.setInt(1, Integer.parseInt(id));
                preparedStatement2.executeUpdate();
            }

            studentVector.add(student);
        }
        
        if(connection != null){
            connection.close();
        }
        return studentVector;
    }
    
    public Vector getDropped() throws Exception{
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Vector<Vector<String>> studentVector = new Vector<Vector<String>>();
        
        Connection connection = DriverManager.getConnection(conStr);
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM Student WHERE [Dropped] = 'Y' ORDER BY 2");
        ResultSet result = preparedStatement.executeQuery();
        
        while(result.next()){
            Vector<String> student = new Vector<String>();
            student.add(result.getString(1));//student id
            student.add(result.getString(2));//student lastName
            student.add(result.getString(3));//student firstName
            student.add(result.getString(4));//student middleName
            
            studentVector.add(student);
        }
        return studentVector;
    }
    
    public Vector getDroppedList() throws Exception{
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Vector<String> studentVector = new Vector<String>();
        
        Connection connection = DriverManager.getConnection(conStr);
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM Student WHERE Dropped = 'Y' ORDER BY 2");
        ResultSet result = preparedStatement.executeQuery();
        
        while(result.next()){
            studentVector.add(result.getString(1));//student id
        }
        return studentVector;
    }
    
    public Vector getStudentForCheck() throws Exception{
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");

        Vector<Vector<Object>> studentVector = new Vector<Vector<Object>>();
        
        Connection connection = DriverManager.getConnection(conStr);
        PreparedStatement preparedStatement = connection.prepareStatement("SELECT * FROM Student WHERE CLASSCODE= '9572' ORDER BY 2");
        ResultSet result = preparedStatement.executeQuery();
        boolean absent = false;
        boolean late = false;
        
        while(result.next()){
            Vector<Object> student = new Vector<Object>();
            student.add(result.getString(1));//student id
            student.add(result.getString(2));//student lastName
            student.add(result.getString(3));//student firstName
            student.add(result.getString(4));//student middleName
            student.add(late);
            student.add(absent);
            

            studentVector.add(student);
        }
        
        if(connection != null){
            connection.close();
        }
        return studentVector;
    }
    
    public void updateAbsences(int id) throws Exception{
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection connection = DriverManager.getConnection(conStr);
        
        PreparedStatement preparedStatement = connection.prepareStatement("UPDATE Student SET Student.[Number of Absences] = [Number of Absences]+1 "
                + "WHERE (([studentID] = ?))");
        preparedStatement.setInt(1, id);
        preparedStatement.executeUpdate();
        
        if(connection != null){
            connection.close();
        }
    }
    
    public void submitClasscard(int id) throws Exception{
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection connection = DriverManager.getConnection(conStr);
        
        PreparedStatement preparedStatement = connection.prepareStatement("UPDATE Student SET Student.[Dropped] = 'N' WHERE (([studentID] = ?))");
        preparedStatement.setInt(1, id);
        preparedStatement.executeUpdate();
        
        if(connection != null){
            connection.close();
        }
    }
    
    public void updateLate(int id) throws Exception {
        Class.forName("sun.jdbc.odbc.JdbcOdbcDriver");
        Connection connection = DriverManager.getConnection(conStr);
        
        PreparedStatement preparedStatement = connection.prepareStatement("UPDATE Student SET Student.[Number of Absences] = [Number of Tardies]+1 "
                + "WHERE (([studentID] = ?))");
        preparedStatement.setInt(1, id);
        preparedStatement.executeUpdate();
        
        if(connection != null){
            connection.close();
        }
    }
    
}
